"""
pd.merge(right, how='inner', on=None, left_on=None, right_on=None)
    right: 合并对象
    how: 合并类型 -- left、right、outer、inner
    on: 标签、列表或数组
    left_on: 标签、列表或数组
    right_on: 标签、列表或数组
"""
import pandas as pd

pd.set_option('display.unicode.east_asian.width', True)
pd.set_option('display.unicode.ambiguous_as_wide', True)

pd.set_option('display.max_rows', 500)
pd.set_option('display.max_columns', 100)
pd.set_option('display.width', 1000)

# 读取数据
excel1 = pd.read_excel('resources/student1.xlsx')
excel2 = pd.read_excel('resources/Student2.xlsx')

print(excel1)
print(excel2)

# 数据合并
new_excel = pd.merge(excel1, excel2, on='编号')  # 根据编号合并
print(new_excel)

new_excel = pd.merge(excel1, excel2, left_index=True, right_index=True)
print(new_excel)

# 以左表的编号为准
new_excel = pd.merge(excel1, excel2, how='left', on='编号')
print(new_excel)

# 将两个表的编号结合 -- 并集
new_excel = pd.merge(excel1, excel2, how='outer', on='编号')
print(new_excel)

# 将两个表的编号结合 -- 交集
new_excel = pd.merge(excel1, excel2, how='inner', on='编号')
print(new_excel)

# 多对一合并
df1 = pd.DataFrame({
    '编号': ['msb1001', 'msb1002', 'msb1003'],
    '姓名': ['张三', '李四', '王五']
})

df2 = pd.DataFrame({
    '编号': ['msb1001', 'msb1001', 'msb1003'],
    '语文': [145, 134, 146],
    '数学': [149, 132, 150],
    '英语': [123, 134, 143],
    '月份': ['1月', '2月', '3月']
})
new_df = pd.merge(df1, df2, on='编号')
print(new_df)

# 多对多的数据合并
df1 = pd.DataFrame({
    '编号': ['msb1001', 'msb1002', 'msb1003', 'msb1003', 'msb1003'],
    '语文': [145, 134, 146, 143, 147]
})
df2 = pd.DataFrame({
    '编号': ['msb1001', 'msb1002', 'msb1003', 'msb1001', 'msb1001'],
    '体育': [14.5, 13.4, 14.6, 14.3, 14.7]
})
new_df = pd.merge(df1, df2)
print(new_df)

"""
pd.concat(objs, axis=0, join='outer' ignore_index=False, keys=None)
    objs: Series、DataFrame对象
    axis: 
    join: inner交集，outer联合
    keys: 使用传递的键，作为最外层构造层次索引
"""
# 纵向表合并：表结构相同 -- 首尾相连
df1 = pd.read_excel('resources/数据合并concat.xlsx', sheet_name='Sheet1')
df2 = pd.read_excel('resources/数据合并concat.xlsx', sheet_name='Sheet2')
df3 = pd.read_excel('resources/数据合并concat.xlsx', sheet_name='Sheet3')

df = pd.concat([df1, df2, df3])
print(df)

df = pd.concat([df1, df2, df3], ignore_index=True)
print(df)

df = pd.concat([df1, df2, df3], keys=['表1', '表2', '表3'])
print(df)

# 横向表合并：列相加
df1 = pd.read_excel('resources/数据合并concat.xlsx', sheet_name='Sheet1')
df4 = pd.read_excel('resources/数据合并concat.xlsx', sheet_name='Sheet4')
print(df1)
print(df4)

df = pd.concat([df1, df4], axis=1)
print(df)

# 两个表的交叉合并
df = pd.concat([df1, df4], axis=1, join='inner')
print(df)
